import datetime
import json

from flask import Flask, request
import pymysql
import time
from apscheduler.schedulers.background import BackgroundScheduler

import cityinfo
import crawler

app = Flask(__name__)


def insert(data, city_id):
    timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    one = json.dumps(data[0], ensure_ascii=False)
    two = json.dumps(data[1], ensure_ascii=False)
    three = json.dumps(data[2], ensure_ascii=False)
    four = json.dumps(data[3], ensure_ascii=False)
    five = json.dumps(data[4], ensure_ascii=False)
    six = json.dumps(data[5], ensure_ascii=False)
    seven = json.dumps(data[6], ensure_ascii=False)

    db = pymysql.connect(host='localhost', user='root', password='123456', database='crawler', charset='utf8')
    cursor = db.cursor()
    sql = "INSERT INTO main(city_id, timestamp, one, two, three, four, five, six, seven) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (
        city_id, timestamp, one, two, three, four, five, six, seven)

    try:
        cursor.execute(sql)
        db.commit()
        return '插入完成！'
    except Exception as e:
        print('错误：' + e)
        db.rollback()
        return 'insert failed'
    finally:
        db.close()


@app.route('/refresh', methods=["POST"])
def refresh():
    data = json.loads(request.data)  # 将json字符串转为dict
    province = data['province']
    city = data['city']
    weather_data, city_id = crawler.get_weather(province, city)
    insert_res = insert(weather_data, city_id)
    res = province + city + insert_res
    return res


@app.route('/select_all', methods=["POST"])
def select_all():
    data = json.loads(request.data)
    province = data['province']
    city = data['city']
    city_id = cityinfo.cityInfo[province][city]["AREAID"]
    db = pymysql.connect(host='localhost', user='root', password='123456', database='crawler')
    cursor = db.cursor()
    sql = f"SELECT * FROM main WHERE city_id = '{city_id}' AND timestamp <= NOW() ORDER BY timestamp DESC LIMIT 1;"
    try:
        cursor.execute(sql)
        data = cursor.fetchall()
        result = []
        result.append({"时间": data[0][2]})
        for item in data[0][3:]:
            result.append(json.loads(item))

        print(json.dumps(result))
        return json.dumps(result)
    except Exception as e:
        print(e)
        return 'fail'
    finally:
        db.close()


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=8080)
